In the last lesson, we looked at the architecture and working of BQ. we have also looked at the use-cases where BQ is the right choice and some exam tips about BQ. In this lesson, we will look at the alternative of MySQL or any SQL-based traditional database and also, the reason to choose the GCP’s Cloud SQL over self-hosted MySQL or PostgreSQL databases. So, let’s start.

Introduction#

Cloud SQL is a fully-managed database service that helps you set up, maintain, manage, and administer your relational databases on the Google Cloud Platform. The main advantage of Cloud SQL is, it takes care of the security, reliability, and maintenance of our SQL servers at a lower level. Normally, CloudSQL is used for CMS backends but we can use it as a normal MySQL database also.

CloudSQL provides read replicas with just a couple of clicks. It also helps in having a high availability database with a failover mechanism. These are some of the offerings of CloudSQL. Let’s see the advantages in detail.

Advantages#

CloudSQL has a lot of advantages over traditional SQL servers. Some of them are:

  • CloudSQL reduces maintenance costs with fully managed relational databases in the cloud.

  • It ensures business continuity with reliable and secure services backed by 24/7 Google’s SRE team

  • Automates database provisioning, storage capacity management, and other time-consuming tasks for us. CloudSQL automatically increases storage for the database.

  • Provides easy integration with existing apps and Google Cloud services like GKE and BigQuery.

  • Provides point-in-time recovery. Because of regular backups, we can restore the DB to its state at an earlier point in time. (Make sure binary logging is enabled.)

  • Secure access via an inbuilt firewall.

  • Easy migration from on-premises or from any other cloud platform with minimum downtime.

When to choose?#

There are multiple ways you can use CloudSQL. The most common are using CloudSQL for CMS backends for platforms like Drupal or WordPress. Google Cloud has a one-click launch mechanism for these CMS software and behind the scene, it uses CloudSQL as a backend DB.

Apart from this, you can use CloudSQL for:

  1. Lift and shift: When you want to lift and shift a SQL database from on-premises to the cloud.

  2. Data analysis: Import data from a CSV text file into Cloud SQL and then analyze it with basic queries. CloudSQL is good for analyzing a medium size of data <100GB.

  3. Compliant and secure database: CloudSQL provides data encryption at rest and in transit. Private connectivity with Virtual Private Cloud and user-controlled network access with firewall protection. CloudSQL is compliant with SSAE 16, ISO 27001, PCI DSS, and HIPAA.

These are some of the points you should keep in mind while choosing the RDBMS in the cloud. CloudSQL’s most frequent use-case is “Lift and Shift” and whenever you come across the words saying “Lift and shift existing database”, CloudSQL is the way to go.

Let’s create our first MySQL instance in the cloud. (You can choose PostgreSQL as well.)

Demo#

In this demo, we will create a SQL instance using UI as well as using gcloud SDK. Let’s first try creating the instance using UI.

Creating a MySQL instance using UI#

To open CloudSQL,

  1. Go to the Main menu > Databases > SQL

  2. Click on the Create Instance button.

widget
  1. Cloud SQL provides managed database service for 3 database service providers. MySQL, PostgreSQL, and SQL Server. For this demo, let’s select MySQL. Once you do that, you will be prompted with a form based on which the instance will be created. Let’s look at the fields one by one.
widget
widget

  • Instance ID: Unique name for the instance.

  • Root Password: You can enter a user-defined password or generate one. This will be used as the password for the “Root” user account. You can also change the password later on from UI. If you want a blank password for the root user selects the “No password” checkbox.

  • MySQL version: If you are lifting and shifting the existing DB, make sure you select the right version for the MySQL. For now, continue with the default one.

  • Select the region closest to you or the users.

    If you expand the configuration options, then you can configure the instance settings like, how much RAM, CPU, and storage type your DB needs. Expand the configuration options.

  • Machine Type and Storage: Select the right machine type for your DB. You need to identify the right machine size for the database. GCP will provide some templates in the dropdown. Select each type and check the difference between their memory, CPU ad pricing. The bigger image will cost more. For the demo purpose, you can choose the smallest one or keep the default one.
Machine Type
  • Storage: Storage will be auto increased but if the DB needs more I/O per second better to select the large machine type. You can change the machine type later on but that needs a minimum downtime as you need to restart the instance to take effect. The best feature of CloudSQL is “automatic storage increase”. The automatic storage increase option is selected by default. Select SSD over HDD for increased I/O speed. That will decrease the latency of retrieving data from the disk.
Storage.
  • Connectivity: This option allows you to control public access to your DB. It has an inbuilt firewall based on IP address and you can whitelist the IP of your network in case you want to access the database on the Internet. The ADD NETWORK button provides an interface to list IPs or network addresses that can access the DB instance over the public internet.
Select the Public IP checkbox.
  • Backup, recovery, and High availability: Enable backup and point in time recovery. The backup keeps creating copies of the data as a snapshot. These options are mandatory for multi-regional instances. The cost of Multi regional instances is higher than the single region instance because of the backup and restore replicas and the resources consumed by them.
Backup options are mandatory for multi regional instances.
  • Maintenance: The instance-level hardware up-gradation maintenance is taken care of by the GCP. We need to select a time window for it. For now, we can keep it default. But in production, select the time frame that receives very low traffic.
You can define a maintenance window.

  • Flags: CloudSQL does not have any superuser for the MySQL database because of security reasons. You can enable Database level flags using this option. The flags are configuration options of the databases. You can configure DB-level settings using flags.

    Keep default values for now and if you want you can add labels for this instance. Labels help in searching and filtering the instances when there are many instances created in the project.

    Once done click the CREATE INSTANCE button. This will take some time to set up and create the MySQL instance.

Click the CREATE INSTANCE button.

Creating a database using UI#

Once the MySQL instance is up and running, you can create the Database using multiple ways.

  1. You can create the database using the SQL command.

  2. You can create the Database using UI.

We will look at the UI method first.

  • Go to the “Databases” options in the left pane under the Overview tab.

  • Click on the Create Database button.

    This will open up a small form that you can fill up. Once done, the database will be created. To create the database using MySQL CLI, we need to first connect to the MySQL instance using any MySQL Client.

    As CloudSQL has an inbuilt firewall, you cannot directly connect using the mysql -h <host> -u root -p command. First, you need to whitelist your IP in the instance’s firewall and then try connecting it. Let’s see it in action using Cloud Shell.

widget
Name the database and click the CREATE button.
Name the database and click the CREATE button.

Connecting to CloudSQL DB#

There are multiple ways to connect to Cloud SQL.

  1. Using Cloud Shell.
  2. Using Compute Engine.
  3. Using a third-party client such as SequelPro.

The easiest will be using Cloud Shell. So, click on “Connect using Cloud Shell”.

widget

As soon as you click on the Cloud Shell connection option a cloud shell window will open up at the bottom and it will run the gcloud CLI command to connect to the CloudSQL.

gcloud sql connect [project name] --user=root --quiet

gcpcourseeducative@cloudshell:~ (gcp-headstart-educative-308308)＄ gcloud sql connect gcpcourse --user=root --quiet
ERROR: (gcloud.sql.connect) PERMISSION_DENIED: Cloud SQL Admin API has not been used in project 392120076017 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview?project=392120076017 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.

If the CloudSQL admin API is not enabled then the command will throw an error and will ask you to enable the API. Enable the admin API by clicking on the URL given in the error and try again.

You need to enable CloudSQL Admin API to connect to CloudSQL instances using CLI.

widget

Once you enable the API. Run the command again, and gcloud CLI performs certain steps to connect to the SQL instance.

  1. It will ask for authorization if not done before. Click on “Authorize” if you get a pop-up to authorize access to the API.

  2. It whitelists the IP of the cloud shell in the connections tab of the SQL instance.

  3. Then it will give you the SQL shell of the instance that means the connection to SQL is successful.

gcpcourseeducative@cloudshell:~ (gcp-headstart-educative-308308)＄ gcloud sql connect gcpcourse --user=root --quiet
Allowlisting your IP for incoming connection for 5 minutes...done.


Connecting to database with SQL user [root].Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.7.32-google-log (Google)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The Connections section in the left pane lists all the whitelisted IPs and the networks which are connected to the instance at the moment. Click on the Connections tab to see the currently whitelisted IP of the cloud shell. (IP can be different from the image below.)

Whitelisted IP of the connected clients.
Whitelisted IP of the connected clients.

Once you are able to connect, you can run SQL queries as you run on any MySQL server.

You can also use MySQL client to connect to your instance from your laptop using mysql -h [IP of instance] -u [user] -p where IP is the CloudSQL instance IP and “user” is the “root” user. But make sure you whitelist your laptop’s IP in the instance’s connection tab.

This completes our introduction to Cloud SQL.

Using gcloud sdk#

The gcloud can be used to create DB instances quickly rather than following these long steps. To create a SQL instance using CLI, type gcloud sql instances create [instance-name]. The command takes the default value for all of the other parameters which are not specified. You can use the --help flag with the above command to check all the configurations present with the help of an argument.

gcpcourseeducative@cloudshell:~ (gcp-headstart-educative-308308)＄ gcloud sql instances create sql-instance-cli
WARNING: Starting with release 233.0.0, you will need to specify either a region or a zone to create an instance.
Creating Cloud SQL instance...done.
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/gcp-headstart-educative-308308/instances/sql-instance-cli].
NAME        DATABASE_VERSION  LOCATION       TIER   PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS

sql-instance-cli  MYSQL_5_7  us-central1-f  db-n1-standard-1 34.66.125.133    -    RUNNABLE

The command will take a max of 3-4 minutes to create the instance. Once the instance is created, you will see the name and IP of the instance immediately.

To create the database in the newly created instance, type gcloud sql databases create [database-name] --instance [instance-name]

gcpcourseeducative@cloudshell:~ (gcp-headstart-educative-308308)＄ gcloud sql databases create demo --instance sql-instance-cli
Creating Cloud SQL database...done.
Created database [demo].
instance: sql-instance-cli
name: demo

To connect to the instance, navigate to the Overview tab and follow the same steps that we followed to connect to the instance created using GUI.

You can use the below terminal to try out the above commands. The terminal will ask you to log in to your GCP account first to configure the terminal with your GCP account.

Use the gcloud sql instances delete [instance-name] command to delete the instance after the demo is over.

Terminal 1
Terminal

Click to Connect...

Limitations#

The limitations of CloudSQL are:

  • No auto-scaling of machines. You need some downtime to apply vertical scaling. There is no horizontal scaling for Cloud SQL.

  • Limited global presence of the server and lack of strong consistency at scale.

Conclusion#

Cloud SQL is good to get started easily or lift and shift existing SQL databases. But when it comes to modern cloud databases, Cloud SQL has some limitations. Limitations like horizontal scaling, global availability in terms of regions. The Cloud Spanner service from GCP addresses these limitations and provides the solution with the capability to scale horizontally without downtime. Overall, the frequent use case of CloudSQL is to lift and shift SQL databases from on-premises to cloud.

BigQuery

Cloud Spanner